﻿/*****************************************************************************/
/* Stored Procedure :: SaveNote                                              */
/*****************************************************************************/
/* Copyright (C) 2003, Perot Systems Corporation. All right reserved.        */
/* Developer: Russ McClelland(russ.mcclelland@ps.net)                        */
/* Started:  10/09/2003                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[SaveNote]
(
    @oid bigint,
    @version rowversion,
    @caseID bigint,
    @text nvarchar( 3000 ),
    @categoryID bigint,
    @entryDate datetime,
    @authorID bigint
)
AS 
BEGIN
  DECLARE @newOID BIGINT
  -- 1. If new record (OID is not set)
  IF ISNULL( @oid, 0 ) <= 0 
  BEGIN
    -- 1.1. Perform insert (OID is identity)
    INSERT INTO Notes( CaseID, Comment, TypeID, EntryDate, TenetID, LastUpdated )
    VALUES( @caseID, @text, @categoryID, @entryDate, @authorID, GetDate() )
    -- 1.2. Determine OID of newly inserted object
    SET @newOID =  SCOPE_IDENTITY()
    -- 1.3. Retutn OID and timestamp of newly inserted object
    SELECT OID, timestamp 
      FROM Notes 
     WHERE OID = @newOID
  END
  -- 2. If OID is set (existing record)
  ELSE BEGIN
    -- 2.1. Attempt updating object (using both OID and timestamp for concurrency reasons)
    UPDATE Notes
      SET CaseID = @caseID,
       Comment = @text,
       TypeID = @categoryID,
       EntryDate = @entryDate,
       TenetID = @authorID,
       LastUpdated = GetDate()
    WHERE ( OID = @oid ) AND ( [timestamp] = @version )
    -- 2.2. If no records were found (and updated) - it means that
    -- another process had updated this record (therefore timestamp will be different)
    -- or record was deleted (OID not found)
    -- Either way, we need to raise exception
    IF @@ROWCOUNT = 0
    BEGIN
      RAISERROR( 99999, 16, 1, 'Note' ) --WITH LOG
    END
    -- 2.3. Record was found - return OID and new timestamp
    ELSE BEGIN 
      SELECT OID, [timestamp]
        FROM Notes
       WHERE OID = @oid
    END
  END
END


